package cmcc.dbi.execJdbcUtil;

import cmcc.dbi.dto.DbQueryDTO;
import cmcc.dbi.entity.InspectionItemRecord;
import cmcc.dbi.exception.ExecutEngineExcption;
import cmcc.dbi.execEnum.DbTypeOfDriverEnum;
import cmcc.dbi.execUtil.AESEncryptionUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
public class PgOfJdbcUtil implements JdbcUtil{
    @Override
    public JSONArray getTargetResult(List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO){
        JSONArray queryAllResult = new JSONArray();

        //巡检项划分
        List<InspectionItemRecord> scriptOfDefaultDb = scriptsLists.stream().filter(s -> s.getInspectedObject() == 0).collect(Collectors.toList());
        List<InspectionItemRecord> scriptOfAllDb = scriptsLists.stream().filter(s -> s.getInspectedObject() == 1).collect(Collectors.toList());

        PgOfJdbcUtil pgOfJdbcUtil = new PgOfJdbcUtil();
        log.info("连接信息: "+dbQueryDTO.getDbIP() + " - " + dbQueryDTO.getDbPort()+" - "+dbQueryDTO.getDbUser()+
                " - "+dbQueryDTO.getDbPass());
        JSONArray resultOfDefaultDb = pgOfJdbcUtil.getResultOfDefaultDb(scriptOfDefaultDb, dbQueryDTO);
        JSONArray resultOfAllDb = pgOfJdbcUtil.getResultOfAllDb(scriptOfAllDb, dbQueryDTO);

        queryAllResult.addAll(resultOfDefaultDb);
        queryAllResult.addAll(resultOfAllDb);

        return queryAllResult;
    }

    private JSONArray getResultOfDefaultDb(List<InspectionItemRecord> scriptOfDefaultDb, DbQueryDTO dbQueryDTO){
        Connection con;
        String url = "jdbc:postgresql://" + dbQueryDTO.getDbIP() + ":" + dbQueryDTO.getDbPort() + "/postgres";
        Statement smt;
        JSONArray queryAllResult = new JSONArray();

        try{
            //加载驱动
            String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
            Class.forName(dbDriver);
            //构建连接
            //con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), dbQueryDTO.getDbPass());
            String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());
            con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), pass);

            if (con != null) {
                log.info(dbQueryDTO.getDbIP() + "的 PostgreSQL 数据库连接成功");
                // 用于执行静态SQL语句并返回其产生的结果的对象
                smt = con.createStatement();

                for(int i=0; i< scriptOfDefaultDb.size(); i++){
                    log.info("The script is -> {}", scriptOfDefaultDb.get(i).getItemName());
                    JSONObject querySingleResult = new JSONObject();
                    try{
                        ResultSet resultSet = smt.executeQuery(scriptOfDefaultDb.get(i).getScript());
                        if(!resultSet.next()){
                            log.info("The result is null");
                            querySingleResult.put("item_id", scriptOfDefaultDb.get(i).getItemId());
                            querySingleResult.put("jdbc_result", null);
                        }else{
                            do{
                                List<Map<String, Object>> resultOfList = new ArrayList<>();
                                for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
                                    Map<String, Object> tmp = new HashMap<>();
                                    Object value = resultSet.getObject(j);
                                    tmp.put(resultSet.getMetaData().getColumnLabel(j), value);
                                    resultOfList.add(tmp);
                                }
                                log.info("The result of script execution is -> {}", resultOfList);
                                querySingleResult.put("item_id", scriptOfDefaultDb.get(i).getItemId());
                                querySingleResult.put("jdbc_result", resultOfList);
                            }while(resultSet.next());
                        }
                    }catch (SQLException e){
                        log.info("脚本出错，请检查");
                        List<Map<String, Object>> resultOfList = new ArrayList<>();
                        Map<String, Object> tmp = new HashMap<>();
                        tmp.put("error_info", e.getMessage());
                        resultOfList.add(tmp);
                        querySingleResult.put("item_id", scriptOfDefaultDb.get(i).getItemId());
                        querySingleResult.put("jdbc_result", resultOfList);
                    }
                    queryAllResult.add(querySingleResult);
                }

                smt.close();
                con.close();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error(dbQueryDTO.getDbIP() + " 的 PostgreSQL 数据库针对默认库执行巡检脚本失败 " + e.getMessage());
        }
        return queryAllResult;
    }

    private JSONArray getResultOfAllDb(List<InspectionItemRecord> scriptOfAllDb, DbQueryDTO dbQueryDTO){
        Connection con;
        String url = "jdbc:postgresql://" + dbQueryDTO.getDbIP() + ":" + dbQueryDTO.getDbPort() + "/";
        Statement smt;
        JSONArray queryAllResult = new JSONArray();
        List<String> dbLists = new ArrayList<>();

        String firstUrl = url + "postgres";
        String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());

        try {
            //加载驱动
            String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
            Class.forName(dbDriver);
            con = DriverManager.getConnection(firstUrl, dbQueryDTO.getDbUser(), pass);

            if(con != null){
                log.info(dbQueryDTO.getDbIP() + "的 PostgreSQL 数据库连接成功");
                // 用于执行静态SQL语句并返回其产生的结果的对象
                smt = con.createStatement();
                ResultSet rsAllDb = smt.executeQuery("select datname from pg_database where datname not in ('template0','template1');");
                while(rsAllDb.next()){
                    String dbName = rsAllDb.getString("datname");
                    dbLists.add(dbName);
                }

                for(int i=0; i<scriptOfAllDb.size(); i++){
                    log.info("The script is -> {}", scriptOfAllDb.get(i).getItemName());
                    JSONObject querySingleResult = new JSONObject();

                    try{
                        List<Map<String, Object>> resultOfList = new ArrayList<>();
                        for(String dbName: dbLists){
                            Statement dbSmt;
                            String dbUrl = url + dbName;
                            Connection dbConn = DriverManager.getConnection(dbUrl, dbQueryDTO.getDbUser(), pass);
                            if(dbConn != null){
                                dbSmt = dbConn.createStatement();
                                ResultSet dbRs = dbSmt.executeQuery(scriptOfAllDb.get(i).getScript());
                                while(dbRs.next()){
                                    for (int j = 1; j <= dbRs.getMetaData().getColumnCount(); j++) {
                                        Map<String, Object> tmp = new HashMap<>();
                                        tmp.put("db_name", dbName);
                                        Object value = dbRs.getObject(j);
                                        tmp.put(dbRs.getMetaData().getColumnLabel(j), value);
                                        resultOfList.add(tmp);
                                    }
                                }
                                dbSmt.close();
                                dbConn.close();
                            }
                            log.info("The result of script execution is -> {}", resultOfList);
                            querySingleResult.put("item_id", scriptOfAllDb.get(i).getItemId());
                            querySingleResult.put("jdbc_result", resultOfList);
                        }
                    }catch (SQLException e){
                        log.info("脚本出错，请检查");
                        List<Map<String, Object>> resultOfList = new ArrayList<>();
                        Map<String, Object> tmp = new HashMap<>();
                        tmp.put("error_info", e.getMessage());
                        resultOfList.add(tmp);
                        querySingleResult.put("item_id", scriptOfAllDb.get(i).getItemId());
                        querySingleResult.put("jdbc_result", resultOfList);
                    }
                    queryAllResult.add(querySingleResult);
                }
                smt.close();
                con.close();
            }
        } catch (SQLException | ClassNotFoundException e) {
            log.error(dbQueryDTO.getDbIP() + " 的 PostgreSQL 数据库针对所有业务库执行巡检脚本失败 " + e.getMessage());
        }
        return queryAllResult;
    }
}
